package com.dingding.manager.modules.dirver.sql;

/**
 * 
* @Description: 司机业务操作相关Sql语句
* @author haoxiaojun 
* @date 2016年12月5日 上午9:47:20
* @since V 2.5.7
 */
public class DriverSql {
	
	/**
	 * 司机List查询
	 * SELECT g.provice_name, f.cityName, h.organizationName, c.trueName, a.sex, a.telPhone, c.idCard, c.jszLssueDate, c.jszValidityPeriod, c.jszLssueDate AS driving, c.permittedType, c.jszFileNumber, b.lisencePlate, b.vehicleType, a.suId, b.suvStatus, c.is_del FROM specialuserinfo a LEFT JOIN specialuservehicleinfo b ON a.userId = b.userId LEFT JOIN dd_special_user_auth_info c ON a.userId =  c.specialUserId LEFT JOIN specialvehicleaudit d ON a.userId = d.userId LEFT JOIN dd_sys_provice_city e ON e.sysCityId = c.cityId LEFT JOIN dd_sys_city f ON e.sysCityId = f.sysCityId LEFT JOIN dd_sys_province g ON e.proviceId = g.provice_id LEFT JOIN dd_organization h ON c.organizationId = h.organizationId WHERE 1 = 1 
	 */ 
	public static final String DRIVER_LIST = "SELECT ul.userStatus,a.userId, g.provice_name, f.cityName, h.organizationName, c.trueName, a.sex, a.telPhone, c.idCard, c.jszLssueDate, c.jszValidityPeriod, c.permittedType, c.jszFileNumber, b.lisencePlate, b.vehicleType, b.vehicleModel, c.specialAuthInfoId, b.suvStatus, c.is_del, b.xszLssueDate, b.vehicleLevelId, b.description AS mark, b.submitTime, b.authType   FROM specialuserinfo a LEFT JOIN specialuservehicleinfo b ON a.userId = b.userId LEFT JOIN dd_special_user_auth_info c ON a.userId =  c.specialUserId LEFT JOIN dd_sys_provice_city e ON e.sysCityId = c.cityId LEFT JOIN dd_sys_city f ON e.sysCityId = f.sysCityId LEFT JOIN dd_sys_province g ON e.proviceId = g.provice_id LEFT JOIN dd_organization h ON c.organizationId = h.organizationId LEFT JOIN specialuserlogin ul ON a.userId = ul.suid WHERE 1 = 1 AND c.is_del = 1 ";
	
	/**
	 * 司机详情
	 * SELECT   a.suId,   b.uvId,   c.specialAuthInfoId,   a.userId,   g.provice_name,   f.cityName,   h.organizationName,   c.trueName,   a.sex,   a.telPhone,   c.idCard,   c.jszLssueDate,   c.jszValidityPeriod,   c.permittedType,   c.jszFileNumber,   b.lisencePlate,   b.vehicleType,   b.vehicleModel,   b.vehicleNumber,   b.xszLssueDate,   b.vehicleOwner,   c.idCardPositiveImage,   c.idCardOppositeImage,   c.jszImage,   b.vehicleImage,   c.jszCopyImage,   b.xszCopyImage,   b.suvStatus,   c.organizationId      AS companyId,   b.xszImage FROM specialuserinfo a   LEFT JOIN specialuservehicleinfo b     ON a.userId = b.userId   LEFT JOIN dd_special_user_auth_info c     ON a.userId = c.specialUserId   LEFT JOIN dd_sys_provice_city e     ON e.sysCityId = c.cityId   LEFT JOIN dd_sys_city f     ON e.sysCityId = f.sysCityId   LEFT JOIN dd_sys_province g     ON e.proviceId = g.provice_id   LEFT JOIN dd_organization h     ON c.organizationId = h.organizationId WHERE 1 = 1 
	 */
	public static final String DRIVER_DETAIL = "SELECT   a.userAvatar, a.suId,   b.uvId,   c.specialAuthInfoId,   a.userId,   g.provice_name,   f.cityName,   h.organizationName,   c.trueName,   a.sex,   a.telPhone,   c.idCard,   c.jszLssueDate,   c.jszValidityPeriod,   c.permittedType,   c.jszFileNumber,   b.lisencePlate,   b.vehicleType,   b.vehicleModel,   b.vehicleNumber,   b.xszLssueDate,   b.vehicleOwner,   c.idCardPositiveImage,   c.idCardOppositeImage,   c.jszImage,   b.vehicleImage,   c.jszCopyImage,   b.xszCopyImage,   b.suvStatus,   b.xszImage,   c.organizationId AS companyId,   g.provice_id AS provinceId,   f.sysCityId AS cityId, b.workLicenseImage, b.workLicenseNumber,  b.authType,  b.vehicleLevelId FROM specialuserinfo a   LEFT JOIN specialuservehicleinfo b     ON a.userId = b.userId   LEFT JOIN dd_special_user_auth_info c     ON a.userId = c.specialUserId   LEFT JOIN dd_sys_provice_city e     ON e.sysCityId = c.cityId   LEFT JOIN dd_sys_city f     ON e.sysCityId = f.sysCityId   LEFT JOIN dd_sys_province g     ON e.proviceId = g.provice_id   LEFT JOIN dd_organization h     ON c.organizationId = h.organizationId WHERE 1 = 1 ";
	
	/**
	 * 根据公司Id获取司机Id
	 * @modify by hufan
	 */
	public static final String GET_USERIDS_BY_COMPANYIDS = "SELECT a.userId  FROM specialuserinfo a  LEFT JOIN dd_special_user_auth_info b   ON a.userId = b.specialUserId LEFT JOIN dd_organization c   ON b.organizationId = c.organizationId WHERE 1=1";

	/**
	 * 根据司机id查询司机的基本信息
	 */
	public static final String GET_DRIVER_BASIC_INFO_BY_DRIVERID = "SELECT u.`userId`, u.`age`, u.`nickName`,u.`occupation`, u.`origin`,u.`sex`,u.`telPhone`,u.`userAvatar`,u.`userLevel`, u.`userTabloidAvatar`, ua.`trueName` FROM `specialuserinfo` u LEFT JOIN `dd_special_user_auth_info` ua ON u.`userId` = ua.`specialUserId` WHERE u.`userId` = ?";
	
}
